from get_chrome import  get_chrombrowser
import time
import random
from parsel import Selector
import win32clipboard
from selenium.webdriver.common.keys import Keys
from selenium.webdriver.support.wait import WebDriverWait
from selenium.webdriver.support import expected_conditions as EC
from selenium.webdriver.common.by import By
import pymysql
# 抓取代理人2021-2022年代理量

db = pymysql.connect(host="localhost",
                     user='root',
                     port=3306,
                     password='123456',
                     database='big_data')
cur = db.cursor()

# def check_code(chrome_browser):
#     # 检查系统是否有异常
#     soup = Selector(chrome_browser.page_source)
#     style = soup.xpath('//div[@id="messagebox"]/@style').extract_first()
#     if style=='display: block;':
#         input('请输入验证码：')
#     st = True
#     while st:
#         if '系统繁忙，请稍后再试！' in chrome_browser.page_source or '抱歉，系统在处理请求时发生了错误，给您带来的不便敬请谅解!' in chrome_browser.page_source:
#             print('系统繁忙，稍后自动刷新')
#             time.sleep(10)
#             chrome_browser.refresh()
#         else:
#             st=False

def parsel_year(chrome_browser,Text):
    #设置复制内容Text
    #打开粘贴板
    win32clipboard.OpenClipboard()
    #清空粘贴板
    win32clipboard.EmptyClipboard()
    #设置复制的内容为Text
    win32clipboard.SetClipboardData(win32clipboard.CF_UNICODETEXT,Text)
    #关闭粘贴板线程
    win32clipboard.CloseClipboard()
    #打开粘贴板
    win32clipboard.OpenClipboard()
    #获取粘贴板内容，传给参数data
    data=win32clipboard.GetClipboardData(win32clipboard.CF_TEXT)
    #需要关闭一下粘贴板线程
    win32clipboard.CloseClipboard()
    el = chrome_browser.find_element_by_xpath('//textarea[@id="textarea"]')
    el.clear()
    time.sleep(1)
    el.click()
    time.sleep(1)
    #输入Control+v模拟粘贴，然后执行搜索操作。
    el.send_keys(Keys.CONTROL, 'v')
    time.sleep(1)
    #通过回车键来代替单击操作，实现查询。
    el.send_keys(Keys.ENTER)
    time.sleep(random.randint(1,3))
    # 跳转到检索内容页面
    handles = chrome_browser.window_handles
    main_handle = handles[0]
    chrome_browser.switch_to.window(handles[-1])
    time.sleep(4)
    if '暂无检索数据' in chrome_browser.page_source:
        patent_number=0
    else:
        try:
            wait = WebDriverWait(chrome_browser,30)
            wait.until(EC.presence_of_element_located((By.XPATH,'//div[@class=" rightMainTop large"]/div[@class="text"]/span')))
        except:
            chrome_browser.refresh()
            wait = WebDriverWait(chrome_browser,30)
            wait.until(EC.presence_of_element_located((By.XPATH,'//div[@class=" rightMainTop large"]/div[@class="text"]/span')))
            time.sleep(5)
        # 解析内容
        soup = Selector(chrome_browser.page_source)
        patent_number = soup.xpath('normalize-space(//div[@class=" rightMainTop large"]/div[@class="text"]/span/text())').extract_first()
    chrome_browser.close()
    chrome_browser.switch_to.window(main_handle)
    return(patent_number)

def parsel_agent_number(chrome_browser,item):
    InstitutionName,OrganizationCode=item
    year_0 = '2021'
    year_1 = '2022'
    Text0="代理机构=(%s) AND 申请日=%s-01-01:%s-12-31"%(OrganizationCode,year_0,year_0)
    Text1="代理机构=(%s) AND 申请日=%s-01-01:%s-12-31"%(OrganizationCode,year_1,year_1)
    print(f'---开始抓取_{InstitutionName}_代理数量信息---')
    agent_count_2021 = parsel_year(chrome_browser,Text0)
    agent_count_2022 = parsel_year(chrome_browser,Text1)
    # 插入数据
    agent_count_sql = f"""INSERT INTO agency_counts (InstitutionName, OrganizationCode, 21_patentNumber,22_patentNumber) VALUES('{InstitutionName}','{OrganizationCode}','{agent_count_2021}','{agent_count_2022}')"""
    print(agent_count_sql)
    cur.execute(agent_count_sql)
    db.commit()
    print(f'---{InstitutionName}_专利代理数量入库成功---')
    time.sleep(random.randint(2, 3))


def parsel_person_number(chrome_browser,item):
    person_name, certificate, FilingNumber, speciality, practice, OrganizationCode, agent_name = item
    year_0 = '2021' #需查询的年份
    year_1 = '2022' 
    Text0="代理机构=(%s) AND 代理人=(%s) AND 申请日=%s-01-01:%s-12-31"%(OrganizationCode,person_name,year_0,year_0)
    Text1="代理机构=(%s) AND 代理人=(%s) AND 申请日=%s-01-01:%s-12-31"%(OrganizationCode,person_name,year_1,year_1)
    print(f'---开始抓取{agent_name}_{person_name}代理数量信息---')
    agent_count_2021 = parsel_year(chrome_browser,Text0)
    agent_count_2022 = parsel_year(chrome_browser,Text1)
    # 插入数据
    agent_count_sql = f"""INSERT INTO agent_person_counts (names, certificate, FilingNumber, speciality, practice,OrganizationCode,InstitutionName,21_patentNumber,22_patentNumber) VALUES('{person_name}','{certificate}','{FilingNumber}','{speciality}','{practice}','{OrganizationCode}','{agent_name}','{agent_count_2021}','{agent_count_2022}')"""
    print(agent_count_sql)
    cur.execute(agent_count_sql)
    db.commit()
    print(f'---{agent_name}_{person_name}_专利代理数量入库成功---')
    # time.sleep(random.randint(2, 3))


if __name__ == '__main__':
    chrome_browser = get_chrombrowser()
    login_url = r'https://tysf.cponline.cnipa.gov.cn/am/#/user/login'
    chrome_browser.get(login_url)
    user = r'42264'
    passwd = r'Xhhg2019@'
    chrome_browser.find_element_by_xpath('//span[text()="代理机构登录"]').click()
    time.sleep(1)
    el_jg = chrome_browser.find_element_by_xpath('//input[@placeholder="代理机构代码"]')
    el_jg.click()
    time.sleep(0.5)
    el_jg.send_keys(user)
    time.sleep(1)
    el_pw = chrome_browser.find_element_by_xpath('//input[@placeholder="请输入密码"]')
    el_pw.click()
    time.sleep(0.5)
    el_pw.send_keys(passwd)
    time.sleep(0.5)
    chrome_browser.find_element_by_xpath('//span[text()="登录"]').click()
    input('滑动验证登录网站：')
    search_url = r"https://pss-system.cponline.cnipa.gov.cn/seniorSearch"
    chrome_browser.get(search_url)
    time.sleep(2)
    # 查询代理人代理量信息
    # 读取库中代理人信息
    # sql = r'''SELECT
    # ins.`names`,
    # ins.`certificate`,
    # ins.`FilingNumber`,
    # ins.`speciality`,
    # if(ins.`practice`='None','',ins.`practice`) as practice,
    # ins.`OrganizationCode`,
    # hub.InstitutionName
    # FROM institutionalagent as ins 
    # left join hubeiprovinceagency as hub
    # on ins.OrganizationCode=hub.OrganizationCode
	# 	left join agent_person_counts as ap
	# 	on ins.`certificate`=ap.`certificate` and ins.OrganizationCode=ap.OrganizationCode
	# 	WHERE ap.OrganizationCode is null'''
    # cur.execute(sql)
    # person_data = cur.fetchall()
    # print('@@成功读取数据库中代理人信息@@')
    # for item in person_data:
    #     parsel_agent_number(chrome_browser, item)

    # 查询代理机构代理量信息
    agent_sql = r'''SELECT
        a.InstitutionName,
        a.OrganizationCode
        FROM 
        hubeiprovinceagency as a
        left join agency_counts as b
        on a.OrganizationCode=b.OrganizationCode
        WHERE b.OrganizationCode is null'''
    cur.execute(agent_sql)
    agent_data = cur.fetchall()
    print('@@成功读取数据库中代理机构信息@@')
    for item in  agent_data:
        parsel_agent_number(chrome_browser,item)
    chrome_browser.quit()